import cx_Oracle
import pandas as pd
import csv

class OracleCoon:
    def __init__(self, host, user, password, sid=None, sever_name=None, conn_type='SN', port=1521):

        # ServerName 连接
        self.user = user
        if sever_name:
            self.conn = cx_Oracle.connect(user, password, f'{host}:{port}/{cx_Oracle.makedsn}')
        # SID 连接
        elif sid:
            dns_tns = cx_Oracle.makedsn(host, port, sid=sid)
            self.conn = cx_Oracle.connect(user, password, dns_tns)
        else:
            raise ValueError('Options sid and service_name cannot be both empty')

    def get_tables(self, user=None):
        """
        查看用户下的表名和视图，未指定则查看连接用户
        :param user:
        :return:
        """
        user = user or self.user
        sql_table = f"select table_name from all_tables where owner='{user}'"
        sql_views = f"select view_name from all_views where owner='{user}'"
        cursor = self.conn.cursor()
        cursor.execute(sql_table)
        rs_table = cursor.fetchall()
        cursor.execute(sql_views)
        rs_view = cursor.fetchall()
        return rs_table, rs_view

    def get_table_desc(self, table_name):
        """
        查看表字段信息
        :param table_name: 指定完整用户 user.table_name
        :return:
        """
        sql = f"select * from {table_name}"
        cursor = self.conn.cursor()
        cursor.execute(sql)
        return cursor.description

    def get_table_data(self, table_name):
        """
        查看表的数据,指定用户
        :param table_name: user.table_name
        :return:
        """
        sql = f"select * from {table_name}"
        df_chunk = pd.read_sql(sql, self.conn, chunksize=1000)
        df_data = pd.concat(df_chunk)
        df_data.to_csv(f'oracle_{table_name}.csv', sep=',', header=True, index=False)
        return df_data




if __name__ == '__main__':
    # conn = OracleCoon('202.115.35.167', 't_datashare_user', 'wVctm$9Z5ZRTQ', sid='bigdata')
    conn = OracleCoon('202.115.54.13', 'DA', '8SrBhp04L$', sid='aleph20') # 图书
    print(conn.get_tables())






